// 操作数库的对象  db
// 数据库：blog_database
// 数据表：pre_notecomment

// SELECT  字段
// FROM  数据表
// WHERE  条件
// INSERT  INTO 插入
// INTO   数据表
// UPDATE   数据表  更新
// DELETE   删除  
// DELETE FROM 数据表


const db = require('../common/db');

module.exports = {

    // 发表随笔评论
    publish(user_id, note_id, comment, createAt, username, imgUrl) {
        return new Promise(async (resolve, reject) => {
            const sql = 'insert into pre_notecomment(user_id,note_id,comment,createAt,username,imgUrl) values(?,?,?,?,?,?)';
            await db.query(sql, [user_id, note_id, comment, createAt, username, imgUrl])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "发表随笔评论成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "发表随笔评论失败",
                            err
                        })
                    }
                )
        })
    },

    // 获取随笔评论列表
    list() {
        return new Promise(async (resolve, reject) => {
            // 编写sql语句 （查询语句）
            const sql = 'SELECT * FROM pre_notecomment';
            // 通过query方法执行sql语句
            await db.query(sql)
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "获取随笔列表成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "获取随笔列表失败",
                            err
                        })
                    }
                )

        })
    },

    // 获取指定用户的随笔评论列表
    listByUser(user_id, page_num = 1, page_size = 10) {
        return new Promise(async (resolve, reject) => {
            // 计算偏移量
            const offset = (page_num - 1) * page_size;

            // 先查询总数
            const countSql = 'SELECT COUNT(*) as total FROM pre_notecomment WHERE user_id = ?';
            const countResult = await db.query(countSql, [user_id]);
            const total = countResult[0].total;

            // 查询当前页数据
            const sql = 'SELECT * FROM pre_notecomment WHERE user_id = ? ORDER BY createAt DESC LIMIT ? OFFSET ?';
            // const sql = 'SELECT * FROM pre_notecomment WHERE user_id = ? LIMIT ? OFFSET ?';
            // 通过query方法执行sql语句
            await db.query(sql, [user_id, parseInt(page_size), parseInt(offset)])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            total: total,
                            msg: "获取用户的随笔评论列表成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "获取用户的随笔评论列表失败",
                            err
                        })
                    }
                )
        })
    },

    // 获取指定随笔的评论列表
    listByNote(note_id) {
        return new Promise(async (resolve, reject) => {
            // 编写sql语句 （查询语句）
            const sql = 'SELECT * FROM pre_notecomment WHERE note_id = ?';
            // 通过query方法执行sql语句
            await db.query(sql, [note_id])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "获取指定随笔的评论列表成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "获取指定随笔评论列表失败",
                            err
                        })
                    }
                )
        })
    },


    // 删除随笔评论
    remove(id) {
        return new Promise(async (resolve, reject) => {
            // 删除语句
            const sql = 'DELETE FROM pre_notecomment WHERE id = ?';
            // 通过query方法执行sql语句
            await db.query(sql, [id])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "删除随笔评论成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "删除随笔评论失败",
                            err
                        })
                    }
                )
        })
    },

    // 修改随笔评论
    modify(comment, createAt, id) {
        return new Promise(async (resolve, reject) => {
            // 修改语句
            const sql = 'UPDATE pre_notecomment SET comment = ?,createAt = ? WHERE id = ?';
            // 通过query方法执行sql语句
            await db.query(sql, [comment, createAt, id])
                .then(
                    result => {
                        resolve({
                            code: 200,
                            msg: "修改随笔评论成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "修改随笔评论失败",
                            err
                        })
                    }
                )
        })
    },

    // 搜索随笔评论
    search(keyword) {
        return new Promise(async (resolve, reject) => {
            // 搜索语句
            const sql = 'SELECT * FROM pre_notecomment WHERE comment LIKE ? or username LIKE ? or user_id LIKE ?';
            // 通过query方法执行sql语句
            await db.query(sql, ['%' + keyword + '%', '%' + keyword + '%', '%' + keyword + '%'])
                .then(
                    result => {
                        if (result.length > 0) {
                            resolve({
                                code: 200,
                                msg: "搜索随笔评论成功",
                                result
                            })
                        } else {
                            resolve({
                                code: -1,
                                msg: "没有搜索到随笔评论",
                                result
                            })
                        }
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "搜索随笔评论失败",
                            err
                        })
                    }
                )
        })
    },

    // 获取分页随笔评论列表
    items(page_num, page_size) {
        return new Promise(async (resolve, reject) => {
            const rows = await db.query(`SELECT count(*) as total FROM pre_notecomment`);

            let page_num2 = ((parseInt(page_num) - 1) * parseInt(page_size))
            let page_size2 = (parseInt(page_size))
            let sql = `select * from pre_notecomment order by createAt DESC limit ${page_num2} , ${page_size2}`
            await db.query(sql)
                .then(
                    result => {
                        resolve({
                            code: 200,
                            total: rows[0].total,
                            msg: "获取分页随笔评论列表成功",
                            result
                        })
                    },
                    err => {
                        reject({
                            code: -1,
                            msg: "获取分页随笔评论列表失败",
                            err
                        })
                    }
                )
        })
    }

}